﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
/*
 * This class will work as interface between the application form and the database, and no one else will do. 
 * 
 */
namespace Personal_Control
{
    class DataBase
    {
        private String data_connection = "Server=localhost;Database=t_control;Uid=root;";
        private MySqlConnection db;
        private String query;
        private MySqlCommand cmd;
        private MySqlDataReader rt;
        public DataBase()
        {
            db = new MySqlConnection(data_connection);
        }
        public Boolean conectDB()
        {
            try
            {
                db.Open();
                return true;
            }
            catch (MySqlException)
            {
                return false;
            }
        }
        public void disconnectDB()
        {
            this.db.Close();
        }
        public Boolean addPersonal(String nombre, String apellido, String cargo, String finger)
        {
            query = "INSERT INTO personal VALUES(0,'" + nombre + "','" + apellido + "','" + cargo + "','" + finger + "')";
            cmd = new MySqlCommand(query, db);
            int result=cmd.ExecuteNonQuery();
            query = "INSERT INTO registro VALUES (0, LAST_INSERT_ID(),'" + DateTime.Today.Date.ToString("yyyy/MM/dd") + "','" + DateTime.Now.ToString("H:mm:ss") + "',0)";
            cmd = new MySqlCommand(query, db);
            result = cmd.ExecuteNonQuery();
            if (result > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        public String addRegister(int id)
        {
            // retrieve the last activity of de personal wich id is the paramether
            query="SELECT activity FROM registro WHERE registro.personal="+id+" ORDER BY date,time DESC LIMIT 1";
            cmd= new MySqlCommand(query,db);

            rt=cmd.ExecuteReader();
            if(rt.Read())
            {
                int last_activity=rt.GetInt16(0);
                int new_activity=1-last_activity;
                rt.Close();

                query = "INSERT INTO registro VALUES (0," + id + ",'" + DateTime.Today.Date.ToString("yyyy/MM/dd") + "','" + DateTime.Now.ToString("H:mm:ss") + "'," + new_activity + ")";
                cmd = new MySqlCommand(query, db);
                cmd.ExecuteNonQuery();
                query ="select personal.nombre, personal.apellido,if(registro.activity=1,'ENTRO','SALIO') from registro inner join personal on registro.personal=personal.idPersonal where personal.idPersonal="+id +" order by registro.date,registro.time desc LIMIT 1";
                cmd = new MySqlCommand(query, db);
                rt = cmd.ExecuteReader();
                rt.Read();
                String mgs = "El empleado " + rt.GetString(0) + " " + rt.GetString(1) + " " + rt.GetString(2) + " de actividad";
                rt.Close();
                return mgs;
            }
            return "Something happend when will attemp store in the database";     
        }
        public String[] fingerPrints()
        {
            String[] f;
            query = "SELECT idPersonal,fingerprint FROM personal";
            cmd = new MySqlCommand(query, db);
            using (rt=cmd.ExecuteReader())
            {
                f = new String[100];
                while (rt.Read())
                {
                    f[rt.GetInt32(0)] = rt.GetString(1);
                }
            }
            return f;
        }
       
    }
}
